﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace Rison.DataAccess.Common
{
    internal sealed class SqlFieldConverter
    {
        private SqlFieldConverter()
        {
        }

        #region "  insert  "
        public static string TurnToInsertSqlString(List<SqlField> fields, string tablename)
        {
            StringBuilder builder = new StringBuilder();
            StringBuilder values = new StringBuilder();

            builder.AppendFormat("insert into {0} (", tablename);

            foreach (SqlField f in fields)
            {
                if (!f.IsIdentity)
                {
                    if (values.Length > 0)
                    {
                        values.Append(",");
                        builder.Append(",");
                    }

                    builder.Append(f.Name);

                    if (f.NeedQuotes)
                        values.AppendFormat("'{0}'", f.Value.ToString());
                    else
                        values.Append(f.Value.ToString()); 
                }
            }

            builder.Append(")");
            values.Insert(0, " values (");
            values.Append(")");

            return builder.ToString() + values.ToString();
        }

        public static string TurnToInsertSqlString(List<SqlField> fields, string tablename, ref List<SqlParameter> sqlParams)
        {
            StringBuilder builder = new StringBuilder();
            StringBuilder values = new StringBuilder();
            sqlParams = new List<SqlParameter>();

            builder.AppendFormat("insert into {0} (", tablename);

            foreach (SqlField f in fields)
            {
                if (!f.IsIdentity)
                {
                    if (values.Length > 0)
                    {
                        values.Append(",");
                        builder.Append(",");
                    }

                    builder.Append(f.Name);
                    values.AppendFormat("@{0}", f.Name);

                    if (f.Value != null)
                        sqlParams.Add(new SqlParameter("@" + f.Name, f.Value));
                    else
                        sqlParams.Add(new SqlParameter("@" + f.Name, DBNull.Value));
                }
            }

            builder.Append(")");
            values.Insert(0, " values (");
            values.Append(")");

            return builder.ToString() + values.ToString();
        }
        #endregion


        #region "  update  "
        public static string TurnToUpdateSqlString(List<SqlField> fields, string tablename)
        {
            StringBuilder condition = new StringBuilder();
            StringBuilder builder = new StringBuilder();

            builder.AppendFormat("update {0} set ", tablename);
            foreach (SqlField f in fields)
            {
                if (f.IsIdentity || f.IsKey)
                {
                    if (condition.Length > 0)
                        condition.Append(" and ");

                    AppendValue(condition, f);   
                }
                else
                {
                    if (!builder.ToString().EndsWith("set "))
                        builder.Append(",");

                    AppendValue(builder, f);   
                }
            }

            if (condition.Length > 0)
                condition.Insert(0, " where ");

            return builder.ToString() + condition.ToString();
        }

        public static string TurnToUpdateSqlString(List<SqlField> fields, string tablename, ref List<SqlParameter> sqlParams)
        {
            StringBuilder condition = new StringBuilder();
            StringBuilder builder = new StringBuilder();
            sqlParams = new List<SqlParameter>();

            builder.AppendFormat("update {0} set ", tablename);
            foreach (SqlField f in fields)
            {
                if (f.IsIdentity || f.IsKey)
                {
                    if (condition.Length > 0)
                        condition.Append(" and ");

                    condition.AppendFormat("{0}=@{1}", f.Name, f.Name);
                }
                else
                {
                    if (!builder.ToString().EndsWith("set "))
                        builder.Append(",");

                    builder.AppendFormat("{0}=@{1}", f.Name, f.Name);
                }

                if (f.Value != null)
                    sqlParams.Add(new SqlParameter("@" + f.Name, f.Value));
                else
                    sqlParams.Add(new SqlParameter("@" + f.Name, DBNull.Value));                
            }

            if (condition.Length > 0)
                condition.Insert(0, " where ");

            return builder.ToString() + condition.ToString();
        }
        #endregion


        #region "  delete  "
        public static string TurnToDeleteSqlString(List<SqlField> fields, string tablename)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("delete from {0} where ", tablename);
            foreach (SqlField f in fields)
            {
                if (!builder.ToString().EndsWith("where "))
                    builder.Append(" and ");

                AppendValue(builder, f);   
            }

            return builder.ToString();
        }
        #endregion


        #region "  retrieve  "
        public static string TurnToRetrieveSqlString(List<SqlField> fields, string tablename)
        {
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("select * from {0} where ", tablename);
            
            foreach (SqlField f in fields)
            {
                if (!builder.ToString().EndsWith("where "))
                    builder.Append(" and ");

                AppendValue(builder, f);                
            }

            return builder.ToString();
        }
        #endregion 


        private static void AppendValue(StringBuilder builder, SqlField f)
        {
            if (f.Value != null)
            {
                if (f.NeedQuotes)
                    builder.AppendFormat("{0}='{1}'", f.Name, f.Value.ToString());
                else
                    builder.AppendFormat("{0}={1}", f.Name, f.Value.ToString());
            }
            else
                builder.AppendFormat("{0}=NULL", f.Name);
        }
    }
}
